﻿Imports Telerik.WinControls

Module ExcelExport
    Sub Export_Inconsistency()
        RadMessageBox.Show("导出选择的图纸中所有的小蓝撇。")
        '得到选择的图纸
        Dim dwgIDs As String = ""
        GetDwgCol(DS, DrawingCol, dwgIDs)
        If DrawingCol.Count = 0 Then
            RadMessageBox.Show("没有选择图纸。")
            Exit Sub
        End If

        Dim audit As New audit(DS.ProjectNumber, "导出不一致性表格", "V1.0")
        audit.Add()
        audit = Nothing
        '得到数据
        SelectStr = <string>
select T7.PATH,t7.name,T5.PLANTITEMTYPE,T5.ITEMTAG,T5.SP_ID,T.PROPNAMEITEM1,T6.SP_ID,T6.PLANTITEMTYPE,T6.ITEMTAG,T.PROPNAMEITEM2,T.description,T.NAME,T.INCONSISTENCYTYPE,T.ISAPPROVED,T.SEVERITY 
from xxxSchemaName.T_INCONSISTENCY t
JOIN xxxSchemaName.T_RELATIONSHIP T2 ON T.SP_RELATIONSHIPID=T2.SP_ID
LEFT JOIN xxxSchemaName.T_REPRESENTATION T3 ON T3.SP_ID=T2.SP_ITEM1ID
LEFT JOIN xxxSchemaName.T_REPRESENTATION T4 ON T4.SP_ID=T2.SP_ITEM2ID
LEFT JOIN xxxSchemaName.T_PLANTITEM T5 ON T5.SP_ID=T3.SP_MODELITEMID
LEFT JOIN xxxSchemaName.T_PLANTITEM T6 ON T6.SP_ID=T4.SP_MODELITEMID
join xxxSchemaName.t_drawing t7 on t7.sp_id=t2.sp_drawingid
where T7.SP_ID IN (XXX)
order by t7.sp_id,t.inconsistencytype
                    </string>.Value.Replace("XXX", dwgIDs)
        dtCreate.Reset()
        'PIDReadDataByDBA(ActivePlantServer, PlantDBSystemUser, PlantDBSystemUserPS, Plant_DBNAME, ActivePlantPIDAppUser, SelectStr, dtCreate)
        MyPIDConn.PIDReadDataByDBA(MyPIDConn.PIDPlant_DBNAME, MyPIDConn.ActivePlantPIDApp_User, SelectStr, dtCreate)
        If dtCreate.Rows.Count = 0 Then
            RadMessageBox.Show("太牛逼了，没有任何一个小蓝撇。")
            Exit Sub
        End If

        dtCreate.Columns.Add("不一致类型") : dtCreate.Columns.Add("对象类型1") : dtCreate.Columns.Add("对象类型2")
        '处理数据
        If dtCreate.Rows.Count > 0 Then
            For index = 0 To dtCreate.Rows.Count - 1
                'path
                dtCreate.Rows(index)("path") = Mid(dtCreate.Rows(index)("path"), 1, InStrRev(dtCreate.Rows(index)("path"), "\") - 1)

                Select Case Convert.ToInt32(dtCreate.Rows(index)("INCONSISTENCYTYPE"))
                    Case 1
                        dtCreate.Rows(index)("不一致类型") = "属性不一致"
                    Case 2
                        dtCreate.Rows(index)("不一致类型") = "Unattached Required Connect Point"
                    Case 3
                        dtCreate.Rows(index)("不一致类型") = "流向不一致"
                    Case 4
                        dtCreate.Rows(index)("不一致类型") = "No applicable rules"
                    Case 5
                        dtCreate.Rows(index)("不一致类型") = "Unattached Connector"
                    Case 6
                        dtCreate.Rows(index)("不一致类型") = "无法评估的不一致"
                    Case Else
                        dtCreate.Rows(index)("不一致类型") = "用户自定义的不一致"
                End Select

                If Not IsDBNull(dtCreate.Rows(index)("PLANTITEMTYPE")) Then
                    Select Case dtCreate.Rows(index)("PLANTITEMTYPE")

                        Case 4
                            dtCreate.Rows(index)("对象类型1") = "Pipe Run"
                        Case 5
                            dtCreate.Rows(index)("对象类型1") = "Signal Run"
                        Case 16
                            dtCreate.Rows(index)("对象类型1") = "Equipment"
                        Case 21
                            dtCreate.Rows(index)("对象类型1") = "Nozzle"
                        Case 35
                            dtCreate.Rows(index)("对象类型1") = "Piping Component"
                        Case 41
                            dtCreate.Rows(index)("对象类型1") = "Instrument"
                        Case 154
                            dtCreate.Rows(index)("对象类型1") = "Pipeline"
                        Case 168
                            dtCreate.Rows(index)("对象类型1") = "Duct Run"
                        Case 170
                            dtCreate.Rows(index)("对象类型1") = "Ducting Component"
                        Case 171
                            dtCreate.Rows(index)("对象类型1") = "Room"
                        Case 172
                            dtCreate.Rows(index)("对象类型1") = "Room Component"
                    End Select
                End If
                If Not IsDBNull(dtCreate.Rows(index)("PLANTITEMTYPE1")) Then
                    Select Case dtCreate.Rows(index)("PLANTITEMTYPE1")
                        Case 4
                            dtCreate.Rows(index)("对象类型2") = "Pipe Run"
                        Case 5
                            dtCreate.Rows(index)("对象类型2") = "Signal Run"
                        Case 16
                            dtCreate.Rows(index)("对象类型2") = "Equipment"
                        Case 21
                            dtCreate.Rows(index)("对象类型2") = "Nozzle"
                        Case 35
                            dtCreate.Rows(index)("对象类型2") = "Piping Component"
                        Case 41
                            dtCreate.Rows(index)("对象类型2") = "Instrument"
                        Case 154
                            dtCreate.Rows(index)("对象类型2") = "Pipeline"
                        Case 168
                            dtCreate.Rows(index)("对象类型2") = "Duct Run"
                        Case 170
                            dtCreate.Rows(index)("对象类型2") = "Ducting Component"
                        Case 171
                            dtCreate.Rows(index)("对象类型2") = "Room"
                        Case 172
                            dtCreate.Rows(index)("对象类型2") = "Room Component"
                    End Select
                End If
            Next
        End If

        dtCreate.Columns.Remove("INCONSISTENCYTYPE") : dtCreate.Columns.Remove("PLANTITEMTYPE") : dtCreate.Columns.Remove("PLANTITEMTYPE1")
        '定义excel相关对象
        Dim MyWB As ClosedXML.Excel.XLWorkbook
        'Dim MySheet As ClosedXML.Excel.IXLWorksheet

        MyWB = New ClosedXML.Excel.XLWorkbook()
        MyWB.AddWorksheet(dtCreate, "蓝撇报告")

        '调出一个 windows的文件夹选择窗口

        '打开文件选择窗口
        Dim SaveExcel As String
        Dim SaveMyFile As New SaveFileDialog With {
            .AddExtension = True,
            .Title = "选择输出文件的保存路径",
            .Filter = "Excel File|*.xlsx",
            .FilterIndex = 1,
            .FileName = "蓝撇报告_" + MyPIDConn.PlantName + ".xlsx"
        }
        If SaveMyFile.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

            SaveExcel = SaveMyFile.FileName
        Else
            MessageBox.Show("请正确选择保存路径", "提示")
            Exit Sub
        End If

        Try
            MyWB.SaveAs(SaveExcel)
            MyWB = Nothing
            MessageBox.Show("Done")
        Catch ex As Exception
            MessageBox.Show("保存成Excel时出现错误。" + ex.Message)
        End Try

        MyWB = Nothing
    End Sub

End Module
